/*
* Nom de classe : ItineraryDAO
*
* Description   : DAO pour Itinerary
*
* Version       : 0.0
*
* Date          : 31/01/2014
*
* Update      : Julian
*/
package org.exia.minisig.model.dao;

import java.io.File;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;

import org.exia.minisig.application.LogFile;
import org.exia.minisig.application.Utils;
import org.exia.minisig.model.database.Database;
import org.exia.minisig.model.object.DescriptibleElement;
import org.exia.minisig.model.object.Itinerary;

/**
* ItineraryDAO
* @version0.0
* @see Itinerary
* @date 31/01/2013
*/
public class ItineraryDAO  extends Database implements DAO{
 //VAR
 private Itinerary itinerary;
 //CONSTRUCTOR
 public ItineraryDAO(){}
 public ItineraryDAO(Itinerary _itinerary) {
  initializeVar(_itinerary);
 }
 public void initializeVar(Itinerary _itinerary) {
	 
  this.itinerary = _itinerary;
 }
 public Itinerary getItinerary() {
  return itinerary;
 }
 public void setItinerary(Itinerary itinerary) {
  this.itinerary = itinerary;
 }
 //OVERRIDE
 @Override
 public void add() {
  try {
	  	this.connect();
   		String jQuery = "INSERT INTO ITINERARY (LabelItinerary, TextItinerary)" +
       "VALUES  ('" + this.itinerary.getLabel() + "', '" + this.itinerary.getText() + "');";
   		Statement addStatement;
	   addStatement = connection.createStatement();
	   addStatement.executeUpdate(jQuery);
	   addStatement.close();
  	} catch (SQLException e) {
	  	LogFile.setEntry("Erreur - ajout itineraire a la bdd\n" + e.getMessage());
		System.out.println(e.getMessage());
  	} finally {
	   this.close();
    }
 }
 @Override
 public void delete() {
	 try {
		  	this.connect();
	   		   		   		
	   		String jQuery = "DELETE FROM ITINERARY WHERE IdItinerary =("+this.itinerary.getId()+");";
	   		Statement addStatement;
		   addStatement = connection.createStatement();
		   addStatement.executeUpdate(jQuery);
		   addStatement.close();
	  	} catch (SQLException e) {
		  	LogFile.setEntry("Erreur - ajout itineraire a la bdd\n" + e.getMessage());
			System.out.println(e.getMessage());
	  	} finally {
		   this.close();
	    }
}
 
 @Override
 public void update() {
	 try {
		  	this.connect();
	   		String jQuery = "UPDATE ITINERARY SET LabelItinerary='" + this.itinerary.getLabel() + "',TextItinerary='" + this.itinerary.getText() + "' WHERE IdItinerary = "+ this.itinerary.getId() + ";";
	   		Statement addStatement;
		   addStatement = connection.createStatement();
		   addStatement.executeUpdate(jQuery);
		   addStatement.close();
	  	} catch (SQLException e) {
		  	LogFile.setEntry("Erreur - update itineraire a la bdd\n" + e.getMessage());
			System.out.println(e.getMessage());
	  	} finally {
		   this.close();
	    }
 }
 @Override
 public ArrayList<Object> getList() {
	 this.connect();
		ArrayList<Object> resultList = new ArrayList<>();
			try {
				Statement st = this.connection.createStatement();
				ResultSet rs= st.executeQuery("SELECT * FROM Itinerary ");
				try {					
					while (rs.next())
					   { 
						Itinerary currentIniterary = new Itinerary();
						currentIniterary.setText(rs.getString("TextItinerary"));
						currentIniterary.setLabel(rs.getString("LabelItinerary"));
						currentIniterary.setId(Integer.parseInt(rs.getString("IdItinerary")));
						resultList.add(currentIniterary);
					    }						
				} catch (SQLException e) {
					System.out.println("Erreur creation liste des news en bdd");
				}
				for (Object object : resultList) {
					ResultSet rss= st.executeQuery("SELECT UrlPictures FROM PICTURES WHERE IdItinerary = "+ ((DescriptibleElement) object).getId()+";");
					try{
						while (rss.next()){
							((DescriptibleElement) object).addImage(rss.getString("UrlPictures"));
						}
					}
					catch(SQLException e1){
						System.err.println("erreur ajout image lors de creation liste des Parcours");
					}
					rss= st.executeQuery("SELECT UrlLink FROM URLS WHERE IdItinerary = "+ ((DescriptibleElement) object).getId()+";");
					try{
						while (rss.next()){
							((DescriptibleElement) object).addURL(rss.getString("UrlLink"));
						}
					}
					catch(SQLException e1){
						System.err.println("erreur ajout url lors de creation liste des news");
					}
				}		
				st.close();
				connection.close();
			} 
			catch (SQLException e2) {
				System.err.println("erreur getList News");
			}			
		return resultList;
 }
 public void linkImages(){
  //1 - Copy
  for (String img: this.itinerary.getImages()) {
   File fileImg = new File(img);
   String test = fileImg.getName();
   String test2= Utils.applicationFolder() + "Itinerary_img/"+test;
   fileImg.renameTo(new File(Utils.applicationFolder() + "Itinerary_img/" + fileImg.getName()));
   try{
   this.connect();
   //QUERY
   String jQuery = "INSERT INTO PICTURES (UrlPictures, IdItinerary )VALUES ('" + test2 + "',"+ this.itinerary.getId() +")";
   //EXECUTION
   Statement addStatement;
   addStatement = connection.createStatement();
   addStatement.executeQuery(jQuery);
   addStatement.close();
   }catch(SQLException e){
   }finally{
    this.close();
   }
   
  }  
 }
 @Override
 public void setID() {
	  try {
		  this.connect();
		  	//RECUPERATION ID
		   	String jQuery = " SELECT IdItinerary FROM ITINERARY ORDER BY IdItinerary DESC";
		   	Statement addStatement;
		   	addStatement = connection.createStatement();
	   		ResultSet result = addStatement.executeQuery(jQuery);
		   	while (result.next()) {
		   		this.itinerary.setId(result.getInt("IdItinerary"));
		    	break;
		   	}
		   	addStatement.close();
		   	result.close();
	  	} catch (SQLException e) {
		  	LogFile.setEntry("Erreur - set id itinerary \n" + e.getCause());
			System.out.println(e.getMessage());
	  	} finally {
	  		this.close();
	    }
}
 public void linkURL() {
	for (String url: this.itinerary.getUrls()) {
		   try{
			   this.connect();
			   //QUERY
			   String jQuery = "INSERT INTO URLS (UrlLink, IdItinerary)  VALUES('"+ url +"'," + this.itinerary.getId() +");";
			   //EXECUTION
			   Statement addStatement;
			   addStatement = connection.createStatement();
			   addStatement.executeQuery(jQuery);
			   addStatement.close();
		   }catch(SQLException e){
		   }finally{
		    this.close();
		   }
	}
}

public void deleteImg(){
	try {
	  	this.connect();
   		   		   		
   		String jQuery = "DELETE FROM PICTURES WHERE IdItinerary =("+this.itinerary.getId()+");";
   		Statement addStatement;
	   addStatement = connection.createStatement();
	   addStatement.executeUpdate(jQuery);
	   addStatement.close();
  	} catch (SQLException e) {
	  	LogFile.setEntry("Erreur - ajout itineraire a la bdd\n" + e.getMessage());
		System.out.println(e.getMessage());
  	} finally {
	   this.close();
    }
}

public void deleteUrl(){
	 try {
		  	this.connect();
	   		   		   		
	   		String jQuery = "DELETE FROM URLS WHERE IdItinerary =("+this.itinerary.getId()+");";
	   		Statement addStatement;
		   addStatement = connection.createStatement();
		   addStatement.executeUpdate(jQuery);
		   addStatement.close();
	  	} catch (SQLException e) {
		  	LogFile.setEntry("Erreur - ajout itineraire a la bdd\n" + e.getMessage());
			System.out.println(e.getMessage());
	  	} finally {
		   this.close();
	    }
}
public void setIDByLabel() {
	this.connect();
		try {
			Statement st = this.connection.createStatement();
			ResultSet rs= st.executeQuery("SELECT * FROM Itinerary WHERE LabelItinerary = '" + this.itinerary.getLabel() + "';");
			try {					
				while (rs.next())
				   { 
					this.itinerary.setId(Integer.parseInt(rs.getString("IdItinerary")));
					break;
				    }						
			} catch (SQLException e) {
				System.out.println("Erreur creation liste des news en bdd");
			}
					
			st.close();
		} 
		catch (SQLException e2) {
			System.err.println("erreur getList News");
		}finally{
			this.close();
		}
}

}

